using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
using ReflectionStudio.Core.Events;
using System;
namespace WPFExcelReport
{ 
    public partial class VVVPACK
    {
        public void SetUpNewRow(ref DataRow dwReturn)
        {

            switch (app.company)
            {
                case "YGR":
                case "YCC":
                default:
                    #region=========
                    dwReturn["VVPMANDANT"] = "00";
                    dwReturn["VVPLIEFSCH"] = "00000000";
                    dwReturn["VVPPACKNR"] = "0000";
                    dwReturn["VVPAENDZL"] = "0";
                    dwReturn["VVPAENDBEN"] = "        ";
                    dwReturn["VVPAENDDAT"] = "0";
                    dwReturn["VVPUEBLIEFSCH"] = "00000000";
                    dwReturn["VVPINPACKNR"] = "0000";
                    dwReturn["VVPPADNR"] = "                         ";
                    dwReturn["VVPPACKTYP"] = "01  ";
                    dwReturn["VVPBRUTTO"] = "0";
                    dwReturn["VVPNETTO"] = "0";
                    dwReturn["VVPTARA"] = "0";
                    dwReturn["VVPWERT"] = "0";
                    dwReturn["VVPFRACHTBE"] = "0000000000";
                    dwReturn["VVPZUSCHLAG"] = "0";
                    dwReturn["VVPFRNETTO"] = "0";
                    dwReturn["VVPNEBENKO"] = "0";
                    dwReturn["VVPWAEHRSLN"] = "   ";
                    dwReturn["VVPLIEFNR"] = "        ";
                    dwReturn["VVPRENR"] = "          ";
                    dwReturn["VVPREPOS"] = "0000";
                    dwReturn["VVPREDAT"] = "0";
                    dwReturn["VVPTPMA"] = "    ";
                    dwReturn["VVPTPMNR"] = "00000000";
                    dwReturn["VVPPORTO"] = "0";
                    dwReturn["VVPRAUFKRS"] = "  ";
                    dwReturn["VVPRAUFLFD"] = "00000000";
                    dwReturn["VVPRAUFPOS"] = "000";
                    dwReturn["VVPTEILNR"] = "0000";
                    dwReturn["VVPVOLUMEN"] = "0";
                    dwReturn["VVPLAENGE"] = "0";
                    dwReturn["VVPBREITE"] = "0";
                    dwReturn["VVPHOEHE"] = "0";
                    dwReturn["VVPKZMANUELL"] = " ";
                    dwReturn["VVPHUELGEW"] = "0";
                    dwReturn["VVPHANGEW"] = "0";
                    dwReturn["VVPSPULEN"] = "2";
                    dwReturn["VVPPKDNR"] = "                         ";
                    dwReturn["VVPGEWGEW"] = " ";
                    dwReturn["VVPENDE"] = " ";
                    dwReturn["VVPSHIPM"] = "     ";
                    dwReturn["VVPAUTOPN"] = " ";
                    dwReturn["VVPSERVICE"] = "   ";
                    dwReturn["VVPOHNEBST"] = " ";
                    dwReturn["VVPRAMPE"] = " ";
                    dwReturn["VVPFILLER"] = "                              ";
                    #endregion
                    break;
                case "JLT":
                    #region=========
                    dwReturn["VVPMANDANT"] = "00";
                    dwReturn["VVPLIEFSCH"] = "00000000";
                    dwReturn["VVPPACKNR"] = "0000";
                    dwReturn["VVPAENDZL"] = "0";
                    dwReturn["VVPAENDBEN"] = "        ";
                    dwReturn["VVPAENDDAT"] = "0";
                    dwReturn["VVPUEBLIEFSCH"] = "00000000";
                    dwReturn["VVPINPACKNR"] = "0000";
                    dwReturn["VVPPADNR"] = "                         ";
                    dwReturn["VVPPACKTYP"] = "01  ";
                    dwReturn["VVPBRUTTO"] = "0";
                    dwReturn["VVPNETTO"] = "0";
                    dwReturn["VVPTARA"] = "0";
                    dwReturn["VVPWERT"] = "0";
                    dwReturn["VVPFRACHTBE"] = "0000000000";
                    dwReturn["VVPZUSCHLAG"] = "0";
                    dwReturn["VVPFRNETTO"] = "0";
                    dwReturn["VVPNEBENKO"] = "0";
                    dwReturn["VVPWAEHRSLN"] = "   ";
                    dwReturn["VVPLIEFNR"] = "        ";
                    dwReturn["VVPRENR"] = "          ";
                    dwReturn["VVPREPOS"] = "0000";
                    dwReturn["VVPREDAT"] = "0";
                    dwReturn["VVPTPMA"] = "    ";
                    dwReturn["VVPTPMNR"] = "00000000";
                    dwReturn["VVPPORTO"] = "0";
                    dwReturn["VVPRAUFKRS"] = "  ";
                    dwReturn["VVPRAUFLFD"] = "00000000";
                    dwReturn["VVPRAUFPOS"] = "000";
                    dwReturn["VVPTEILNR"] = "0000";
                    dwReturn["VVPVOLUMEN"] = "0";
                    dwReturn["VVPLAENGE"] = "0";
                    dwReturn["VVPBREITE"] = "0";
                    dwReturn["VVPHOEHE"] = "0";
                    dwReturn["VVPKZMANUELL"] = " ";
                    dwReturn["VVPHUELGEW"] = "0";
                    dwReturn["VVPHANGEW"] = "0";
                    dwReturn["VVPSPULEN"] = "2";
                    dwReturn["VVPPKDNR"] = "                         ";
                    dwReturn["VVPGEWGEW"] = " ";
                    dwReturn["VVPENDE"] = " ";
                    dwReturn["VVPSHIPM"] = "     ";
                    dwReturn["VVPAUTOPN"] = " ";
                    dwReturn["VVPSERVICE"] = "   ";
                    dwReturn["VVPOHNEBST"] = " ";
                    dwReturn["VVPRAMPE"] = " ";
                    dwReturn["VVPFILLER"] = "                              ";
                    #endregion
                    break;
            }
        }
        public void DeleteVVPACK(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM VVPACK "
                       + " WHERE VVPMANDANT=:VVPMANDANT: AND VVPLIEFSCH=:VVPLIEFSCH: AND VVPPACKNR=:VVPPACKNR: ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }

        public void InsertVVPACK(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "YGR":
                case "YCC":
                default:
                    or1.CommandText = "INSERT INTO VVPACK ( VVPMANDANT,VVPLIEFSCH,VVPPACKNR,VVPAENDZL,VVPAENDBEN,VVPAENDDAT,VVPUEBLIEFSCH,VVPINPACKNR,VVPPADNR,VVPPACKTYP,VVPBRUTTO,VVPNETTO,VVPTARA,VVPWERT,VVPFRACHTBE,VVPZUSCHLAG,VVPFRNETTO,VVPNEBENKO,VVPWAEHRSLN,VVPLIEFNR,VVPRENR,VVPREPOS,VVPREDAT,VVPTPMA,VVPTPMNR,VVPPORTO,VVPRAUFKRS,VVPRAUFLFD,VVPRAUFPOS,VVPTEILNR,VVPVOLUMEN,VVPLAENGE,VVPBREITE,VVPHOEHE,VVPKZMANUELL,VVPHUELGEW,VVPHANGEW,VVPSPULEN,VVPPKDNR,VVPGEWGEW,VVPENDE,VVPSHIPM,VVPAUTOPN,VVPSERVICE,VVPOHNEBST,VVPRAMPE,VVPFILLER)"
                   + " VALUES (:VVPMANDANT:,:VVPLIEFSCH:,:VVPPACKNR:,:VVPAENDZL:,:VVPAENDBEN:,:VVPAENDDAT:,:VVPUEBLIEFSCH:,:VVPINPACKNR:,:VVPPADNR:,:VVPPACKTYP:,:VVPBRUTTO:,:VVPNETTO:,:VVPTARA:,:VVPWERT:,:VVPFRACHTBE:,:VVPZUSCHLAG:,:VVPFRNETTO:,:VVPNEBENKO:,:VVPWAEHRSLN:,:VVPLIEFNR:,:VVPRENR:,:VVPREPOS:,:VVPREDAT:,:VVPTPMA:,:VVPTPMNR:,:VVPPORTO:,:VVPRAUFKRS:,:VVPRAUFLFD:,:VVPRAUFPOS:,:VVPTEILNR:,:VVPVOLUMEN:,:VVPLAENGE:,:VVPBREITE:,:VVPHOEHE:,:VVPKZMANUELL:,:VVPHUELGEW:,:VVPHANGEW:,:VVPSPULEN:,:VVPPKDNR:,:VVPGEWGEW:,:VVPENDE:,:VVPSHIPM:,:VVPAUTOPN:,:VVPSERVICE:,:VVPOHNEBST:,:VVPRAMPE:,:VVPFILLER:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case "JLT":
                    or1.CommandText = "INSERT INTO VVPACK ( VVPMANDANT,VVPLIEFSCH,VVPPACKNR,VVPAENDZL,VVPAENDBEN,VVPAENDDAT,VVPUEBLIEFSCH,VVPINPACKNR,VVPPADNR,VVPPACKTYP,VVPBRUTTO,VVPNETTO,VVPTARA,VVPWERT,VVPFRACHTBE,VVPZUSCHLAG,VVPFRNETTO,VVPNEBENKO,VVPWAEHRSLN,VVPLIEFNR,VVPRENR,VVPREPOS,VVPREDAT,VVPTPMA,VVPTPMNR,VVPPORTO,VVPRAUFKRS,VVPRAUFLFD,VVPRAUFPOS,VVPTEILNR,VVPVOLUMEN,VVPLAENGE,VVPBREITE,VVPHOEHE,VVPKZMANUELL,VVPHUELGEW,VVPHANGEW,VVPSPULEN,VVPPKDNR,VVPGEWGEW,VVPENDE,VVPSHIPM,VVPAUTOPN,VVPSERVICE,VVPOHNEBST,VVPRAMPE,VVPFILLER)"
                    + " VALUES (:VVPMANDANT:,:VVPLIEFSCH:,:VVPPACKNR:,:VVPAENDZL:,:VVPAENDBEN:,:VVPAENDDAT:,:VVPUEBLIEFSCH:,:VVPINPACKNR:,:VVPPADNR:,:VVPPACKTYP:,:VVPBRUTTO:,:VVPNETTO:,:VVPTARA:,:VVPWERT:,:VVPFRACHTBE:,:VVPZUSCHLAG:,:VVPFRNETTO:,:VVPNEBENKO:,:VVPWAEHRSLN:,:VVPLIEFNR:,:VVPRENR:,:VVPREPOS:,:VVPREDAT:,:VVPTPMA:,:VVPTPMNR:,:VVPPORTO:,:VVPRAUFKRS:,:VVPRAUFLFD:,:VVPRAUFPOS:,:VVPTEILNR:,:VVPVOLUMEN:,:VVPLAENGE:,:VVPBREITE:,:VVPHOEHE:,:VVPKZMANUELL:,:VVPHUELGEW:,:VVPHANGEW:,:VVPSPULEN:,:VVPPKDNR:,:VVPGEWGEW:,:VVPENDE:,:VVPSHIPM:,:VVPAUTOPN:,:VVPSERVICE:,:VVPOHNEBST:,:VVPRAMPE:,:VVPFILLER:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
            }
            Connect2Proxy(or1);
        }
        public DataSet QueryVVPACK(DataRow dwData, int option = 1)
        {
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM VVPACK WHERE  ROWNUM<2 ";
                    break;
                case 1:
                    or1.CommandText = "SELECT * FROM VVPACK WHERE VVPMANDANT=:VVPMANDANT: AND VVPLIEFSCH=:VVPLIEFSCH: AND VVPPACKNR=:VVPPACKNR:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 2:
                    or1.CommandText = "SELECT P.* FROM VVPACK P JOIN VVLIEF V ON VVPMANDANT=VVMANDANT AND VVPLIEFSCH=VVLIEFSCH JOIN VVTLIEF T ON V.VVMANDANT=VVTMANDANT AND VVLIEFSCH=VVTLIEFSCH"
               + " WHERE VVTMANDANT=:VVTMANDANT: AND VVTAUFKRS =:VVTAUFKRS: AND VVTAUFLFD=:VVTAUFLFD:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 3:
                    or1.CommandText = "SELECT VVLIEFSCH,P.* FROM VVPACK P  JOIN VVLIEF ON VVLIEFSCH=VVPLIEFSCH  "
                               + " WHERE VVMANDANT=:VVMANDANT: AND  VVLIEFSCH=:VVLIEFSCH:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 4:
                    or1.CommandText = "SELECT * FROM VVPACK WHERE VVPMANDANT=:VVPMANDANT: AND VVPLIEFSCH=:VVPLIEFSCH:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
            }
            Result = Connect2Proxy(or1);
            Result.Tables[0].TableName = "VVPACK";
            return Result;
        }

        public void UpdateVVPACK(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {

            or1 = OracleDB.CreateCommand();
            if (option == 0)
            {
                switch (app.company)
                {
                    case "YGR":
                    case "YCC":
                    default:
                        option = 1;
                        break;
                    case "JLT":
                        option = 2;
                        break;
                }
            }
            switch (option)
            {
                case 1:
                    #region====Command====
                    or1.CommandText = "UPDATE VVPACK SET VVPMANDANT=:VVPMANDANT:,VVPLIEFSCH=:VVPLIEFSCH:,VVPPACKNR=:VVPPACKNR:,VVPAENDZL=:VVPAENDZL:,VVPAENDBEN=:VVPAENDBEN:,VVPAENDDAT=:VVPAENDDAT:,VVPUEBLIEFSCH=:VVPUEBLIEFSCH:,VVPINPACKNR=:VVPINPACKNR:,VVPPADNR=:VVPPADNR:,VVPPACKTYP=:VVPPACKTYP:,VVPBRUTTO=:VVPBRUTTO:,VVPNETTO=:VVPNETTO:,VVPTARA=:VVPTARA:,VVPWERT=:VVPWERT:,VVPFRACHTBE=:VVPFRACHTBE:,VVPZUSCHLAG=:VVPZUSCHLAG:,VVPFRNETTO=:VVPFRNETTO:,VVPNEBENKO=:VVPNEBENKO:,VVPWAEHRSLN=:VVPWAEHRSLN:,VVPLIEFNR=:VVPLIEFNR:,VVPRENR=:VVPRENR:,VVPREPOS=:VVPREPOS:,VVPREDAT=:VVPREDAT:,VVPTPMA=:VVPTPMA:,VVPTPMNR=:VVPTPMNR:,VVPPORTO=:VVPPORTO:,VVPRAUFKRS=:VVPRAUFKRS:,VVPRAUFLFD=:VVPRAUFLFD:,VVPRAUFPOS=:VVPRAUFPOS:,VVPTEILNR=:VVPTEILNR:,VVPVOLUMEN=:VVPVOLUMEN:,VVPLAENGE=:VVPLAENGE:,VVPBREITE=:VVPBREITE:,VVPHOEHE=:VVPHOEHE:,VVPKZMANUELL=:VVPKZMANUELL:,VVPHUELGEW=:VVPHUELGEW:,VVPHANGEW=:VVPHANGEW:,VVPSPULEN=:VVPSPULEN:,VVPPKDNR=:VVPPKDNR:,VVPGEWGEW=:VVPGEWGEW:,VVPENDE=:VVPENDE:,VVPSHIPM=:VVPSHIPM:,VVPAUTOPN=:VVPAUTOPN:,VVPSERVICE=:VVPSERVICE:,VVPOHNEBST=:VVPOHNEBST:,VVPRAMPE=:VVPRAMPE:,VVPFILLER=:VVPFILLER:"
                        + " WHERE VVPMANDANT=:VVPMANDANT: AND VVPLIEFSCH=:VVPLIEFSCH: AND VVPPACKNR=:VVPPACKNR: ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    #endregion
                    break;
                case 2:
                    #region====Command====
                    or1.CommandText = "UPDATE VVPACK SET VVPMANDANT=:VVPMANDANT:,VVPLIEFSCH=:VVPLIEFSCH:,VVPPACKNR=:VVPPACKNR:,VVPAENDZL=:VVPAENDZL:,VVPAENDBEN=:VVPAENDBEN:,VVPAENDDAT=:VVPAENDDAT:,VVPUEBLIEFSCH=:VVPUEBLIEFSCH:,VVPINPACKNR=:VVPINPACKNR:,VVPPADNR=:VVPPADNR:,VVPPACKTYP=:VVPPACKTYP:,VVPBRUTTO=:VVPBRUTTO:,VVPNETTO=:VVPNETTO:,VVPTARA=:VVPTARA:,VVPWERT=:VVPWERT:,VVPFRACHTBE=:VVPFRACHTBE:,VVPZUSCHLAG=:VVPZUSCHLAG:,VVPFRNETTO=:VVPFRNETTO:,VVPNEBENKO=:VVPNEBENKO:,VVPWAEHRSLN=:VVPWAEHRSLN:,VVPLIEFNR=:VVPLIEFNR:,VVPRENR=:VVPRENR:,VVPREPOS=:VVPREPOS:,VVPREDAT=:VVPREDAT:,VVPTPMA=:VVPTPMA:,VVPTPMNR=:VVPTPMNR:,VVPPORTO=:VVPPORTO:,VVPRAUFKRS=:VVPRAUFKRS:,VVPRAUFLFD=:VVPRAUFLFD:,VVPRAUFPOS=:VVPRAUFPOS:,VVPTEILNR=:VVPTEILNR:,VVPVOLUMEN=:VVPVOLUMEN:,VVPLAENGE=:VVPLAENGE:,VVPBREITE=:VVPBREITE:,VVPHOEHE=:VVPHOEHE:,VVPKZMANUELL=:VVPKZMANUELL:,VVPHUELGEW=:VVPHUELGEW:,VVPHANGEW=:VVPHANGEW:,VVPSPULEN=:VVPSPULEN:,VVPPKDNR=:VVPPKDNR:,VVPGEWGEW=:VVPGEWGEW:,VVPENDE=:VVPENDE:,VVPSHIPM=:VVPSHIPM:,VVPAUTOPN=:VVPAUTOPN:,VVPSERVICE=:VVPSERVICE:,VVPOHNEBST=:VVPOHNEBST:,VVPRAMPE=:VVPRAMPE:,VVPFILLER=:VVPFILLER:"
                        + " WHERE VVPMANDANT=:VVPMANDANT: AND VVPLIEFSCH=:VVPLIEFSCH: AND VVPPACKNR=:VVPPACKNR: ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    #endregion
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
    }
}
